-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema kazaku5d_test
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema kazaku5d_test
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `kazaku5d_test` DEFAULT CHARACTER SET utf8 ;
USE `kazaku5d_test` ;

-- -----------------------------------------------------
-- Table `kazaku5d_test`.`academic_years`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kazaku5d_test`.`academic_years` ;

CREATE TABLE IF NOT EXISTS `kazaku5d_test`.`academic_years` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `year` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `kazaku5d_test`.`specialization`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kazaku5d_test`.`specialization` ;

CREATE TABLE IF NOT EXISTS `kazaku5d_test`.`specialization` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(10) NOT NULL,
  `name_spec` VARCHAR(100) NOT NULL,
  `qualification` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `kazaku5d_test`.`plan_list`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kazaku5d_test`.`plan_list` ;

CREATE TABLE IF NOT EXISTS `kazaku5d_test`.`plan_list` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `year_approval` INT NOT NULL,
  `idspec` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_plan_spec_idx` (`idspec` ASC) VISIBLE,
  CONSTRAINT `fk_plan_spec`
    FOREIGN KEY (`idspec`)
    REFERENCES `kazaku5d_test`.`specialization` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `kazaku5d_test`.`groups`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kazaku5d_test`.`groups` ;

CREATE TABLE IF NOT EXISTS `kazaku5d_test`.`groups` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `course` SMALLINT(6) NOT NULL DEFAULT '1',
  `name` VARCHAR(45) NOT NULL,
  `idspec` INT(11) NOT NULL,
  `begin_year` INT(45) NULL DEFAULT NULL,
  `id_uchplan` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Groups_Specialties1_idx` (`idspec` ASC) VISIBLE,
  INDEX `fk_group_planList_idx` (`id_uchplan` ASC) VISIBLE,
  CONSTRAINT `fk_Groups_Specialties1_idx`
    FOREIGN KEY (`idspec`)
    REFERENCES `kazaku5d_test`.`specialization` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_group_planList`
    FOREIGN KEY (`id_uchplan`)
    REFERENCES `kazaku5d_test`.`plan_list` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `kazaku5d_test`.`items`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kazaku5d_test`.`items` ;

CREATE TABLE IF NOT EXISTS `kazaku5d_test`.`items` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `item` VARCHAR(45) NOT NULL COMMENT 'Сокращенное наименование предмета',
  `description` VARCHAR(150) NULL DEFAULT NULL COMMENT 'Полное наименование предмета',
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `kazaku5d_test`.`teachers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kazaku5d_test`.`teachers` ;

CREATE TABLE IF NOT EXISTS `kazaku5d_test`.`teachers` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `fullName` VARCHAR(45) NOT NULL,
  `teacherDismissed` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Преподаватель уволен  (0 - нет, 1 - да)',
  `dateDismissed` DATE NULL DEFAULT NULL COMMENT 'Дата увольнения',
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `kazaku5d_test`.`nagruzka`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kazaku5d_test`.`nagruzka` ;

CREATE TABLE IF NOT EXISTS `kazaku5d_test`.`nagruzka` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `idAcadYear` INT(11) NOT NULL,
  `idTeacher` INT(11) NOT NULL,
  `idGroup` INT(11) NOT NULL,
  `podGroup` TINYINT(4) NOT NULL DEFAULT '0',
  `idItem` INT(11) NOT NULL,
  `hoursSumm` SMALLINT(6) NOT NULL DEFAULT '0',
  `hours1Sem` SMALLINT(6) NOT NULL DEFAULT '0',
  `hours2Sem` SMALLINT(6) NOT NULL DEFAULT '0',
  `prakt` TINYINT(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  INDEX `fk_Nagruzka_UchPlan1_idx` (`idItem` ASC) VISIBLE,
  INDEX `fk_Nagruzka_Groups1_idx` (`idGroup` ASC) VISIBLE,
  INDEX `fk_Nagruzka_Teachers1_idx` (`idTeacher` ASC) VISIBLE,
  INDEX `fk_Nagruzka_AcademicYears1_idx` (`idAcadYear` ASC) VISIBLE,
  CONSTRAINT `fk_Nagruzka_AcademicYears1_idx`
    FOREIGN KEY (`idAcadYear`)
    REFERENCES `kazaku5d_test`.`academic_years` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Nagruzka_Groups1_idx`
    FOREIGN KEY (`idGroup`)
    REFERENCES `kazaku5d_test`.`groups` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Nagruzka_Teachers1_idx`
    FOREIGN KEY (`idTeacher`)
    REFERENCES `kazaku5d_test`.`teachers` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Nagruzka_UchPlan1_idx`
    FOREIGN KEY (`idItem`)
    REFERENCES `kazaku5d_test`.`items` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `kazaku5d_test`.`users`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kazaku5d_test`.`users` ;

CREATE TABLE IF NOT EXISTS `kazaku5d_test`.`users` (
  `login` VARCHAR(45) NOT NULL COMMENT 'Логин пользователя',
  `password` VARCHAR(45) NOT NULL COMMENT 'Пароль пользователя',
  `realname` VARCHAR(45) NOT NULL DEFAULT 'unknown' COMMENT 'Имя пользователя',
  `role` VARCHAR(45) NOT NULL DEFAULT 'user',
  PRIMARY KEY (`login`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `kazaku5d_test`.`reg_log`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kazaku5d_test`.`reg_log` ;

CREATE TABLE IF NOT EXISTS `kazaku5d_test`.`reg_log` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `login` VARCHAR(45) NOT NULL,
  `datetime` DATETIME NOT NULL,
  `auth_result` BIT(1) NOT NULL DEFAULT b'0',
  `users_login` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_reg_log_users1_idx` (`login` ASC) VISIBLE,
  CONSTRAINT `fk_reg_log_users1_idx`
    FOREIGN KEY (`login`)
    REFERENCES `kazaku5d_test`.`users` (`login`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `kazaku5d_test`.`uchPlan`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kazaku5d_test`.`uchPlan` ;

CREATE TABLE IF NOT EXISTS `kazaku5d_test`.`uchPlan` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `id_plan` INT NOT NULL COMMENT 'Год утверждения учебного плана',
  `index` VARCHAR(15) NOT NULL,
  `idItem` INT(11) NOT NULL,
  `zach` TINYINT(3) UNSIGNED NULL DEFAULT NULL COMMENT 'Зачет',
  `difzach` TINYINT(3) UNSIGNED NULL DEFAULT NULL COMMENT 'Дифференцированный зачет',
  `ekzam` TINYINT(3) UNSIGNED NULL DEFAULT NULL COMMENT 'Экзамен',
  `totalLoad` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Общий объем часов',
  `selfStudy` SMALLINT(5) UNSIGNED NULL DEFAULT '0' COMMENT 'Самостоятельная работа',
  `total` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Всего занятий',
  `theoryUr` SMALLINT(5) UNSIGNED NULL DEFAULT '0' COMMENT 'Количество часов теории',
  `practiceUr` SMALLINT(5) UNSIGNED NULL DEFAULT '0' COMMENT 'Количество часов лабораторных и практикческих занятий',
  `consultation` SMALLINT(5) UNSIGNED NULL DEFAULT '0' COMMENT 'Консультация',
  `attestation` SMALLINT(5) UNSIGNED NULL DEFAULT '0' COMMENT 'Промежуточная аттестация',
  `sem1` SMALLINT(5) UNSIGNED NULL DEFAULT '0',
  `sem2` SMALLINT(5) UNSIGNED NULL DEFAULT '0',
  `sem3` SMALLINT(5) UNSIGNED NULL DEFAULT '0',
  `sem4` SMALLINT(5) UNSIGNED NULL DEFAULT '0',
  `sem5` SMALLINT(5) UNSIGNED NULL DEFAULT '0',
  `sem6` SMALLINT(5) UNSIGNED NULL DEFAULT '0',
  `sem7` SMALLINT(5) UNSIGNED NULL DEFAULT '0',
  `sem8` SMALLINT(5) UNSIGNED NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  INDEX `fk_UchPlan_Items_idx` (`idItem` ASC) VISIBLE,
  INDEX `fk_UchPlan_PlanList_idx` (`id_plan` ASC) VISIBLE,
  CONSTRAINT `fk_UchPlan_Items_idx`
    FOREIGN KEY (`idItem`)
    REFERENCES `kazaku5d_test`.`items` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_UchPlan_PlanList`
    FOREIGN KEY (`id_plan`)
    REFERENCES `kazaku5d_test`.`plan_list` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;







ALTER TABLE `kazaku5d_test`.`groups` 
ADD CONSTRAINT `fk_group_planList`
  FOREIGN KEY (`id_uchplan`)
  REFERENCES `kazaku5d_test`.`plan_list` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `kazaku5d_test`.`groups` 
ADD INDEX `fk_group_planList_idx` (`id_uchplan` ASC);
